package com.bk.dao;

import com.bk.model.Book;
import com.bk.model.BookType;
import com.bk.util.DbUtil;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

import javax.swing.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author Ayi
 */

public class BookDao {
    // 获取数据库中的用户表，以结果集返回
    public ResultSet getBookTable() throws SQLException, ClassNotFoundException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from book";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        return rs;
    }

    // 往数据库图书表添加新的一本书
    public boolean addBook(String bname, int binventory, String btype) throws SQLException {
        if (bname.isEmpty()) {
            return false;
        }
        Connection connection = DbUtil.getConnection();
        String sql = "insert into book values (null, ?, ?, ?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, "《" + bname + "》");
        ps.setString(2, Integer.toString(binventory));
        ps.setString(3, btype);
        try {
            ps.executeUpdate();
            new BookTypeDao().updateAddInventory(binventory, btype);
            return true;
        }catch (MySQLIntegrityConstraintViolationException e) {
            return false;
        }
    }

    // 在数据库图书表删除一本书
    public boolean deleteBook(String bname, int binventory, String btype) throws SQLException {
        if (bname.isEmpty()) {
            return false;
        }
        Connection connection = DbUtil.getConnection();
        String sql = "delete from book where bname = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, bname);
        try {
            ps.executeUpdate();
            new BookTypeDao().updateSubInventory(binventory, btype);
            return true;
        }catch (MySQLIntegrityConstraintViolationException e) {
            return false;
        }
    }

    // 根据图书id找到一本书
    public Book searchBookById(String idOrNameInfo) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from book where bid = " + idOrNameInfo;
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            Book resBook = new Book(rs.getString("bname"));
            resBook.setBid(Integer.valueOf(rs.getString("bid")));
            resBook.setBtype(rs.getString("btype"));
            resBook.setBinventory(Integer.valueOf(rs.getString("binventory")));
            return resBook;
        }else{
            return null;
        }
    }

    // 根据图书名找到一本书
    public Book searchBookByName(String idOrNameInfo) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from book where bname = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, "《" + idOrNameInfo + "》");
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            Book resBook = new Book(rs.getString("bname"));
            resBook.setBid(Integer.valueOf(rs.getString("bid")));
            resBook.setBtype(rs.getString("btype"));
            resBook.setBinventory(Integer.valueOf(rs.getString("binventory")));
            return resBook;
        }else{
            return null;
        }
    }

    // 编辑数据库图书表某一本书的信息
    public boolean editBook(Book book, String bname, int binventory, String btype) throws SQLException {
        Connection connection = DbUtil.getConnection();

        String sql0 = "update book set bname = ? where bid = ?";
        PreparedStatement ps0 = connection.prepareStatement(sql0);
        ps0.setString(1, "《" + bname + "》");
        ps0.setString(2, Integer.toString(book.getBid()));

        String sql1 = "update book set binventory = ? where bid = ?";
        PreparedStatement ps1 = connection.prepareStatement(sql1);
        ps1.setString(1, Integer.toString(binventory));
        ps1.setString(2, Integer.toString(book.getBid()));

        String sql2 = "update book set btype = ? where bid = ?";
        PreparedStatement ps2 = connection.prepareStatement(sql2);
        ps2.setString(1, btype);
        ps2.setString(2, Integer.toString(book.getBid()));

        int r0 = ps0.executeUpdate();
        int r1 = ps1.executeUpdate();
        int r2 = ps2.executeUpdate();

        new BookTypeDao().updateInventory(book.getBinventory(), book.getBtype(), binventory);
        return r0 > 0 && r1 > 0 && r2 > 0 ? true : false;
    }

    // 图书类型的删除功能 更新图书表
    public void updateBook0(String btname) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "delete from book where btype = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, btname);
        ps.executeUpdate();
    }

    // 借阅界面的借阅功能 更新图书表
    public boolean updateSubBook(Book book) throws SQLException {
        if (book.getBinventory() == 0) {
             return false;
        }
        System.out.println(book.getBinventory());
        Connection connection = DbUtil.getConnection();
        String sql = "update book set binventory = ? where bid = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, Integer.toString(book.getBinventory() - 1));
        ps.setString(2, Integer.toString(book.getBid()));
        ps.executeUpdate();
        return true;
    }
    // 借阅界面的退订功能 更新图书表
    public boolean updateAddBook(Book book, int uborrow) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "update book set binventory = ? where bid = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, Integer.toString(book.getBinventory() + uborrow));
        ps.setString(2, Integer.toString(book.getBid()));
        ps.executeUpdate();
        return true;
    }
}
